package com.kay;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sound.midi.Soundbank;

/**
 * Created by Kay on 2018/11/22
 * excel 表格转换为es的dsl插入语句
 * 6个sheet  basic、diagnosis、medical、exam、checkup、followup
 */
public class ClientExcel2DSL {
    private static Logger logger = LoggerFactory.getLogger(ClientExcel2DSL.class);
//    public static void main(String[] args) throws IOException {
//        Properties prop = new Properties();
//        InputStream inputStream = ClientExcel2DSL.class.getClassLoader().getResourceAsStream("conf/application.properties");
//        prop.load(new InputStreamReader(inputStream,"utf-8"));
//        String filepath = prop.getProperty("filepath");
//        System.out.println(filepath);
//        File file = new File(filepath);
//        System.out.println(file.getName());
//        ClientExcel2DSL excel2DSL = getInstance();
//        Map<String,Object> resultStr = excel2DSL.readExcel(file,2);  // 第三行是 header
////        System.out.println(resultStr);
////        for (String key :resultStr.keySet()){
////            System.out.println("============= key:" + key);
////            System.out.println("============= value:" + resultStr.get(key));
////        }
//    }

    /**
     * 获取一个实例
     * @return
     */
    private static ClientExcel2DSL getInstance(){
        return new ClientExcel2DSL();
    }

    /**
     * 文件过滤
     * @param file
     * @return
     */
    private boolean filenameFilter(File file){
        boolean endsWith = false;
        if(file != null){
            String fileName = file.getName();
            endsWith = fileName.endsWith(".xls") || fileName.endsWith(".xlsx");
        }
        return endsWith;
    }

    /**
     * 获取表头行
     * @param sheet
     * @param index
     * @return
     */
    private Row getHeaderRow(Sheet sheet, int index){
        Row headerRow = null;
        if(sheet!=null){
            headerRow = sheet.getRow(index);
        }
        return headerRow;
    }

    /**
     *
     * @param row
     * @param cellIndex
     * @param formula
     * @return
     */
    private Object getCellValue(Row row,int cellIndex,FormulaEvaluator formula){
        Cell cell = row.getCell(cellIndex);
        if(cell != null){
            switch (cell.getCellType()) {
                //String类型
                case Cell.CELL_TYPE_STRING:
                    return cell.getRichStringCellValue().getString();

                //number类型
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return cell.getDateCellValue().getTime();
                    } else {
                        return cell.getNumericCellValue();
                    }
                    //boolean类型
                case Cell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue();
                //公式
                case Cell.CELL_TYPE_FORMULA:
                    return formula.evaluate(cell).getNumberValue();
                default:
                    return null;
            }
        }
        return null;
    }

    public Map<String,Object> readExcel(File file,int headerIndex) {
        Map<String, Object> maps = new HashMap<String, Object>();
        if (!filenameFilter(file)) {
            return null;
        } else {
            try {
                //加载excel表格
                WorkbookFactory workbookFactory = new WorkbookFactory();
                Workbook workbook = workbookFactory.create(file);


                for(int sheetnum = 0 ; sheetnum < workbook.getNumberOfSheets();sheetnum++) {
//                    System.out.println(workbook.getSheetAt(sheetnum).getSheetName());
                    StringBuilder sb = new StringBuilder();
                    //读取第一个sheet basic页
                    Sheet sheet = workbook.getSheetAt(sheetnum);
                    String sheetName = sheet.getSheetName();
//                    sb.append("POST /client_info/" + sheetName + "/_bulk\n");
                    logger.info("================================" + sheetName);
                    //读取表头行
                    Row headerRow = getHeaderRow(sheet, headerIndex);
                    //读取数据
                    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                    for (int i = headerIndex + 1; i <= sheet.getLastRowNum(); i++) {
                        Row dataRow = sheet.getRow(i);
                        Map<String, Object> map = new HashMap<String, Object>();

                        for (int j = 0; j < dataRow.getLastCellNum(); j++) {
                            //表头为key
                            String key = headerRow.getCell(j).getRichStringCellValue().getString();
                            Cell cell = dataRow.getCell(j);
                            Object value = null;
                            if(cell == null) continue;
                            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {

                                Date d = cell.getDateCellValue();
                                DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                value = dateFormat.format(d);
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                value = cell.getStringCellValue();
                            }
                            if (!value.equals("") && !value.equals("null") && value != null) {
                                if (sheetName.equals("basic") || !key.equals("id")) // 非basic  id 不读取
                                    map.put(key, value);
                            }
                        }
                        // 一行取完

                        String data = JSON.toJSONString(map);
                        final Map<String, Object> indexMap = new HashMap<String, Object>();
                        if (sheetName.equals("basic")) {
                            indexMap.put("_id", map.get("id"));
                            sb.append(JSON.toJSONString(new HashMap<String, Object>() {{
                                put("index", indexMap);
                            }}));
                            sb.append("\n");
                        } else if (sheetName.equals("followup")) {
                            indexMap.put("_id", map.get("flow_id"));
                            indexMap.put("parent", map.get("party_id"));
                            sb.append(JSON.toJSONString(new HashMap<String, Object>() {{
                                put("index", indexMap);
                            }}));
                            sb.append("\n");
                        } else {
                            indexMap.put("parent", map.get("party_id"));
                            sb.append(JSON.toJSONString(new HashMap<String, Object>() {{
                                put("index", indexMap);
                            }}));
                            sb.append("\n");
                        }
                        sb.append(data + "\n");

                    }
                    sb.append( "\n");
                    maps.put(sheetName,sb.toString());
                }
                return maps;

            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }
}
